package model.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import model.dto.Fixture;
import utility.Database;
import utility.DateTool;
import utility.Log;

public class FixtureModel extends Database {
	public boolean insert(Fixture dto) {
		String sql = "insert into tbl_fixture(fixture_id,fixture_date,fixture_time,club_home_id,club_away_id,league_id,tournament_id,season_id,week_number) values(null,?,?,?,?,?,?,?,?)";
		try {
			this.connect();
			PreparedStatement pstmt = con.prepareStatement(sql);
			pstmt.setDate(1, (java.sql.Date)dto.getDate());
			pstmt.setString(2, dto.getTime());
			pstmt.setInt(3, dto.getClubHomeId());
			pstmt.setInt(4, dto.getClubAwayId());
			pstmt.setInt(5, dto.getLeagueId());
			pstmt.setInt(6, dto.getTournamentId());
			pstmt.setInt(7, dto.getSeasonId());
			pstmt.setInt(8, dto.getWeekNumber());
			pstmt.executeUpdate();
			return true;
		} catch (SQLException e) {
			Log.write(e);
		} finally {
			this.close();
		}

		return false;
	}

	public boolean delete(int fixture_id) {
		String sql = "delete from tbl_fixture where fixture_id=?";
		try {
			this.connect();
			PreparedStatement pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, fixture_id);
			pstmt.executeUpdate();
			return true;
		} catch (SQLException e) {
			Log.write(e);
		} finally {
			this.close();
		}
		return false;
	}

	public boolean update(Fixture dto) {
		String sql = "update tbl_fixture set fixture_date=?,fixture_time=?,club_home_id=?,club_away_id=?,league_id=?,tournament_id=?,season_id=?,week_number=? where fixture_id=?";
		try {
			this.connect();
			PreparedStatement pstmt = con.prepareStatement(sql);
			pstmt.setDate(1, (java.sql.Date)dto.getDate());
			pstmt.setString(2, dto.getTime());
			pstmt.setInt(3, dto.getClubHomeId());
			pstmt.setInt(4, dto.getClubAwayId());
			pstmt.setInt(5, dto.getLeagueId());
			pstmt.setInt(6, dto.getTournamentId());
			pstmt.setInt(7, dto.getSeasonId());
			pstmt.setInt(8, dto.getWeekNumber());
			pstmt.setInt(9, dto.getFixtureId());
			pstmt.executeUpdate();
			return true;
		} catch (SQLException e) {
			Log.write(e);
		} finally {
			this.close();
		}
		return false;
	}

	public Fixture get(int fixture_id) {
		String sql = "select * from tbl_fixture where fixture_id=?";
		try {
			this.connect();
			PreparedStatement pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, fixture_id);
			ResultSet rs = pstmt.executeQuery();
			if (rs.next()) {
				Fixture dto = new Fixture();
				dto.setFixtureId(fixture_id);
				dto.setDate(rs.getDate("fixture_date"));
				dto.setTime(rs.getString("fixture_time"));
				dto.setClubHomeId(rs.getInt("club_home_id"));
				dto.setClubAwayId(rs.getInt("club_away_id"));
				dto.setLeagueId(rs.getInt("league_id"));
				dto.setTournamentId(rs.getInt("tournament_id"));
				dto.setSeasonId(rs.getInt("season_id"));
				dto.setWeekNumber(rs.getInt("week_number"));
				return dto;
			}
		} catch (SQLException e) {
			Log.write(e);
		} finally {
			this.close();
		}
		return null;
	}

	public ResultSet list(String search, int limit, int page) {
		String sql = "select * from tbl_fixture where fixture_name like ? ORDER BY country_id DESC limit ?,?";
		try {
			this.connect();
			PreparedStatement pstmt = con.prepareStatement(sql);
			pstmt.setString(1, "%" + search + "%");
			pstmt.setInt(2, (page - 1) * limit);
			pstmt.setInt(3, limit);
			ResultSet rs = pstmt.executeQuery();
			return rs;
		} catch (SQLException e) {
			Log.write(e);
		} finally {
			this.close();
		}
		return null;
	}

	public ResultSet list(int limit, int page) {
		return list("", limit, page);
	}

	public int count(String search) {
		String sql = "select count(*) from tbl_country where country_name like ?";
		try {
			this.connect();
			PreparedStatement pstmt = con.prepareStatement(sql);
			pstmt.setString(1, "%" + search + "%");
			ResultSet rs = pstmt.executeQuery();
			return rs.next() ? rs.getInt(1) : 0;
		} catch (SQLException e) {
			Log.write(e);
		} finally {
			this.close();
		}
		return 0;
	}

	public int count() {
		return count("");
	}
	public static void main(String[] args) {
		Fixture dto=new Fixture();
		FixtureModel dao=new FixtureModel();
		dto.setDate(DateTool.getDateSQL("2015/2/19"));
		dto.setTime("8:30");
		dto.setClubHomeId(3);
		dto.setClubAwayId(4);
		dto.setTournamentId(2);
		dto.setSeasonId(2);
		dto.setWeekNumber(2);
		dto.setLeagueId(4);
		dto.setFixtureId(1);
		for(int i=0;i<15;i++){
			System.out.println(dao.insert(dto));
			
		}
		
		//System.out.println(dao.update(dto));
		//System.out.println(dao.delete(1));
	}

}
